import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;


import java.io.FileOutputStream;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

/**
 * Created by us on 2016/10/22.
 */
public class CreatCell {
   /* public  static void main(String []args) throws Exception{

        CreatCell newCell = new CreatCell();
        newCell.create();

    }*/
   public void create()throws Exception {
       HSSFWorkbook workbook = new HSSFWorkbook();
       String filePath = "data/exp_1.xls";

       HSSFSheet sheet = workbook.createSheet("Test_1");

       for (int i = 0; i < 2; i++) {
           HSSFRow row = sheet.createRow(i);
           //设置日期格式
           HSSFCell cell = row.createCell(0);
           Date date = new Date();
           cell.setCellValue(new Date());
           HSSFCellStyle style = workbook.createCellStyle();
           style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
       //设置保留2位小数--使用Excel内嵌的格式
           cell = row.createCell(1);
           cell.setCellValue(12.3456789);
           style = workbook.createCellStyle();
           style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
           cell.setCellStyle(style);
       //设置货币格式--使用自定义的格式
           cell = row.createCell(2);
           cell.setCellValue(12345.6789);
           style = workbook.createCellStyle();
           style.setDataFormat(workbook.createDataFormat().getFormat("￥#,##0"));
           cell.setCellStyle(style);
       //设置百分比格式--使用自定义的格式
           cell = row.createCell(3);
           cell.setCellValue(0.123456789);
           style = workbook.createCellStyle();
           style.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
           cell.setCellStyle(style);
       //设置科学计数法格式--使用自定义的格式
           cell = row.createCell(4);
           cell.setCellValue(12345);
           style = workbook.createCellStyle();
           style.setDataFormat(workbook.createDataFormat().getFormat("0.00E+00"));
           cell.setCellStyle(style);



/*//合并行
       cell=row.createCell(6);
       cell.setCellValue("合并行");
       region=new CellRangeAddress(0, 8, 6, 6);
       sheet.addMergedRegion(region);*/

        /*   FileOutputStream out = new FileOutputStream(filePath);
           workbook.write(out);//保存Excel文件
           out.close();//关闭文件流
           System.out.println("OK!");*/
       }


       HSSFRow row = sheet.createRow(3);
       HSSFCell cell = row.createCell(0);
       HSSFCellStyle style = workbook.createCellStyle();
           cell = row.createCell(6);

       //合并列
       // HSSFCell cell=row.createCell(0);
       cell.setCellValue("合并列");
       CellRangeAddress region = new CellRangeAddress(5, 5, 0, 1);
       sheet.addMergedRegion(region);

       //单元格对齐
           cell.setCellValue("单元格对齐");
           HSSFCellStyle style1=workbook.createCellStyle();
           style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
           style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
           style.setWrapText(true);//自动换行
           style.setIndention((short)1);//缩进
           style.setRotation((short)0);//文本旋转，这里的取值是从-90到90，而不是0-180度。
           cell.setCellStyle(style);
//计算A1和A2的和
           row = sheet.createRow(2);
           row.createCell(0).setCellFormula("sum(A1,A2)");

       FileOutputStream out = new FileOutputStream(filePath);
       workbook.write(out);//保存Excel文件
       out.close();//关闭文件流
       System.out.println("OK!");
   }
}
